package com.tpc.control.jpa;

import java.util.Date;
import java.util.List;
import java.util.logging.Level;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import com.tpc.control.util.Formatting;

/**
 * A data access object (DAO) providing persistence and search support for
 * SaleData entities. Transaction control of the save(), update() and delete()
 * operations must be handled externally by senders of these methods or must be
 * manually added to each of these methods for data to be persisted to the JPA
 * datastore.
 * 
 * @see com.tpc.control.jpa.SaleData
 * @author MyEclipse Persistence Tools
 */

public class SaleDataDAO implements ISaleDataDAO {
	// property constants
	public static final String CUSTOMER = "customer";
	public static final String CUSTOMER_NAME = "customerName";
	public static final String CUSTOMER_PO_NO = "customerPoNo";
	public static final String SALE_ORG = "saleOrg";
	public static final String DISTR_CHANNEL = "distrChannel";
	public static final String PLANT = "plant";
	public static final String MATERIAL = "material";
	public static final String ORDER_AMOUNT = "orderAmount";
	public static final String COMPLETE_AMOUNT = "completeAmount";
	public static final String REMAIN_AMOUNT = "remainAmount";
	public static final String END_USER_CODE = "endUserCode";
	public static final String END_USER_NAME = "endUserName";
	public static final String COMPLETE_FLAG = "completeFlag";
	public static final String QUANTITY = "quantity";
	public static final String UOM = "uom";
	public static final String PRICE = "price";
	public static final String CURRENCY = "currency";

	private EntityManager getEntityManager() {
		return EntityManagerHelper.getEntityManager();
	}

	/**
	 * Perform an initial save of a previously unsaved SaleData entity. All
	 * subsequent persist actions of this entity should use the #update()
	 * method. This operation must be performed within the a database
	 * transaction context for the entity's data to be permanently saved to the
	 * persistence store, i.e., database. This method uses the
	 * {@link javax.persistence.EntityManager#persist(Object)
	 * EntityManager#persist} operation.
	 * 
	 * <pre>
	 * EntityManagerHelper.beginTransaction();
	 * SaleDataDAO.save(entity);
	 * EntityManagerHelper.commit();
	 * </pre>
	 * 
	 * @param entity
	 *            SaleData entity to persist
	 * @throws RuntimeException
	 *             when the operation fails
	 */
	public void save(SaleData entity) {
		EntityManagerHelper.log("saving SaleData instance", Level.INFO, null);
		try {
			getEntityManager().persist(entity);
			EntityManagerHelper.log("save successful", Level.INFO, null);
		} catch (RuntimeException re) {
			EntityManagerHelper.log("save failed", Level.SEVERE, re);
			throw re;
		}
	}

	/**
	 * Delete a persistent SaleData entity. This operation must be performed
	 * within the a database transaction context for the entity's data to be
	 * permanently deleted from the persistence store, i.e., database. This
	 * method uses the {@link javax.persistence.EntityManager#remove(Object)
	 * EntityManager#delete} operation.
	 * 
	 * <pre>
	 * EntityManagerHelper.beginTransaction();
	 * SaleDataDAO.delete(entity);
	 * EntityManagerHelper.commit();
	 * entity = null;
	 * </pre>
	 * 
	 * @param entity
	 *            SaleData entity to delete
	 * @throws RuntimeException
	 *             when the operation fails
	 */
	public void delete(SaleData entity) {
		EntityManagerHelper.log("deleting SaleData instance", Level.INFO, null);
		try {
			entity = getEntityManager().getReference(SaleData.class,
					entity.getId());
			getEntityManager().remove(entity);
			EntityManagerHelper.log("delete successful", Level.INFO, null);
		} catch (RuntimeException re) {
			EntityManagerHelper.log("delete failed", Level.SEVERE, re);
			throw re;
		}
	}

	/**
	 * Persist a previously saved SaleData entity and return it or a copy of it
	 * to the sender. A copy of the SaleData entity parameter is returned when
	 * the JPA persistence mechanism has not previously been tracking the
	 * updated entity. This operation must be performed within the a database
	 * transaction context for the entity's data to be permanently saved to the
	 * persistence store, i.e., database. This method uses the
	 * {@link javax.persistence.EntityManager#merge(Object) EntityManager#merge}
	 * operation.
	 * 
	 * <pre>
	 * EntityManagerHelper.beginTransaction();
	 * entity = SaleDataDAO.update(entity);
	 * EntityManagerHelper.commit();
	 * </pre>
	 * 
	 * @param entity
	 *            SaleData entity to update
	 * @return SaleData the persisted SaleData entity instance, may not be the
	 *         same
	 * @throws RuntimeException
	 *             if the operation fails
	 */
	public SaleData update(SaleData entity) {
		EntityManagerHelper.log("updating SaleData instance", Level.INFO, null);
		try {
			SaleData result = getEntityManager().merge(entity);
			EntityManagerHelper.log("update successful", Level.INFO, null);
			return result;
		} catch (RuntimeException re) {
			EntityManagerHelper.log("update failed", Level.SEVERE, re);
			throw re;
		}
	}

	public SaleData findById(SaleDataId id) {
		EntityManagerHelper.log("finding SaleData instance with id: " + id,
				Level.INFO, null);
		try {
			SaleData instance = getEntityManager().find(SaleData.class, id);
			return instance;
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find failed", Level.SEVERE, re);
			throw re;
		}
	}

	/**
	 * Find all SaleData entities with a specific property value.
	 * 
	 * @param propertyName
	 *            the name of the SaleData property to query
	 * @param value
	 *            the property value to match
	 * @return List<SaleData> found by query
	 */
	@SuppressWarnings("unchecked")
	public List<SaleData> findByProperty(String propertyName, final Object value) {
		EntityManagerHelper.log("finding SaleData instance with property: "
				+ propertyName + ", value: " + value, Level.INFO, null);
		try {
			final String queryString = "select model from SaleData model where model."
					+ propertyName + "= :propertyValue";
			Query query = getEntityManager().createQuery(queryString);
			query.setParameter("propertyValue", value);
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

	public List<SaleData> findByCustomer(Object customer) {
		return findByProperty(CUSTOMER, customer);
	}

	public List<SaleData> findByCustomerName(Object customerName) {
		return findByProperty(CUSTOMER_NAME, customerName);
	}

	public List<SaleData> findByCustomerPoNo(Object customerPoNo) {
		return findByProperty(CUSTOMER_PO_NO, customerPoNo);
	}

	public List<SaleData> findBySaleOrg(Object saleOrg) {
		return findByProperty(SALE_ORG, saleOrg);
	}

	public List<SaleData> findByDistrChannel(Object distrChannel) {
		return findByProperty(DISTR_CHANNEL, distrChannel);
	}

	public List<SaleData> findByPlant(Object plant) {
		return findByProperty(PLANT, plant);
	}

	public List<SaleData> findByMaterial(Object material) {
		return findByProperty(MATERIAL, material);
	}

	public List<SaleData> findByOrderAmount(Object orderAmount) {
		return findByProperty(ORDER_AMOUNT, orderAmount);
	}

	public List<SaleData> findByCompleteAmount(Object completeAmount) {
		return findByProperty(COMPLETE_AMOUNT, completeAmount);
	}

	public List<SaleData> findByRemainAmount(Object remainAmount) {
		return findByProperty(REMAIN_AMOUNT, remainAmount);
	}

	public List<SaleData> findByEndUserCode(Object endUserCode) {
		return findByProperty(END_USER_CODE, endUserCode);
	}

	public List<SaleData> findByEndUserName(Object endUserName) {
		return findByProperty(END_USER_NAME, endUserName);
	}

	public List<SaleData> findByCompleteFlag(Object completeFlag) {
		return findByProperty(COMPLETE_FLAG, completeFlag);
	}

	public List<SaleData> findByQuantity(Object quantity) {
		return findByProperty(QUANTITY, quantity);
	}

	public List<SaleData> findByUom(Object uom) {
		return findByProperty(UOM, uom);
	}

	public List<SaleData> findByPrice(Object price) {
		return findByProperty(PRICE, price);
	}

	public List<SaleData> findByCurrency(Object currency) {
		return findByProperty(CURRENCY, currency);
	}

	/**
	 * Find all SaleData entities.
	 * 
	 * @return List<SaleData> all SaleData entities
	 */
	@SuppressWarnings("unchecked")
	public List<SaleData> findAll() {
		EntityManagerHelper.log("finding all SaleData instances", Level.INFO,
				null);
		try {
			final String queryString = "select model from SaleData model";
			Query query = getEntityManager().createQuery(queryString);
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find all failed", Level.SEVERE, re);
			throw re;
		}
	}

	/* Customize */	
	public List<SaleData> findByReport(
			Object company1, Object company2,
			Object division1, Object division2,
			Object customer1, Object customer2,
			Object saleOrder1, Object saleOrder2,
			Object saleDueDate1, Object saleDueDate2,
			Object customerPo1, Object customerPo2,
			Object endUser) {
		boolean firstCondition = true;
		EntityManagerHelper.log("findByReport SaleData instance", Level.INFO, null);
		try {
			System.out.println("company: "+company1+"::"+company2);
			System.out.println("division: "+division1+"::"+division2);
			System.out.println("customer: "+customer1+"::"+customer2);
			System.out.println("saleOrder: "+saleOrder1+"::"+saleOrder2);
			System.out.println("saleDueDate: "+saleDueDate1+"::"+saleDueDate2);
			System.out.println("customerPo: "+customerPo1+"::"+customerPo2);
			System.out.println("endUser: "+endUser);
			
			String[] company = Formatting.generateFromToText(company1, company2, 4);
			String[] division = Formatting.generateFromToText(division1, division2, 2);
			String[] customer = Formatting.generateFromToText(customer1, customer2, 10);
			String[] saleOrder = Formatting.generateFromToText(saleOrder1, saleOrder2, 10);
			Date[] saleDueDate = Formatting.generateFromToDate(saleDueDate1, saleDueDate2);
			String[] customerPo = Formatting.generateFromToText(customerPo1, customerPo2, 35);
			String endUserSelect = "%" + endUser + "%";
			String queryString = "select model from SaleData model ";
			if (!((String)company1).equals("") || !((String)company2).equals("")) {
				if (firstCondition) {
					queryString += " where ";
					firstCondition = false;
				} else {
					queryString += " and ";
				}
				queryString += " (model.company.companyId " + " between :propertyValue1 "
					+ " and :propertyValue2) ";
			}
			if (!((String)division1).equals("") || !((String)division2).equals("")) {
				if (firstCondition) {
					queryString += " where ";
					firstCondition = false;
				} else {
					queryString += " and ";
				}
				queryString += " (model.division.division " + " between :propertyValue3 "
					+ " and :propertyValue4) ";
			}
			if (!((String)customer1).equals("") || !((String)customer2).equals("")) {
				if (firstCondition) {
					queryString += " where ";
					firstCondition = false;
				} else {
					queryString += " and ";
				}
				queryString += " (model.customer " + " between :propertyValue5 "
				+ " and :propertyValue6) ";
			}
			if (!((String)saleOrder1).equals("") || !((String)saleOrder2).equals("")) {
				if (firstCondition) {
					queryString += " where ";
					firstCondition = false;
				} else {
					queryString += " and ";
				}
				queryString += " (model.id.saleOrder " + " between :propertyValue7 "
				+ " and :propertyValue8) ";
			}
			if (saleDueDate1!=null || saleDueDate2!=null) {
				if (firstCondition) {
					queryString += " where ";
					firstCondition = false;
				} else {
					queryString += " and ";
				}
				queryString += " (model.dueDate " + " between :propertyValue9 "
				+ " and :propertyValue10) ";
			}
			if (!((String)customerPo1).equals("") || !((String)customerPo2).equals("")) {
				if (firstCondition) {
					queryString += " where ";
					firstCondition = false;
				} else {
					queryString += " and ";
				}
				queryString += " (model.customerPoNo " + " between :propertyValue11 "
				+ " and :propertyValue12) ";
			}
			if (!((String)endUser).equals("")) {
				if (firstCondition) {
					queryString += " where ";
					firstCondition = false;
				} else {
					queryString += " and ";
				}
				
				queryString += " (model.endUserCode " + " LIKE :propertyValue13 ) ";
			}
			
			queryString += " order by model.division.division ";
			System.out.println("queryString: "+queryString);
			
			Query query = getEntityManager().createQuery(queryString);
			if (!((String)company1).equals("") || !((String)company2).equals("")) {
				query.setParameter("propertyValue1", company[0]);
				query.setParameter("propertyValue2", company[1]);
			}
			if (!((String)division1).equals("") || !((String)division2).equals("")) {
				query.setParameter("propertyValue3", division[0]);
				query.setParameter("propertyValue4", division[1]);
			}
			if (!((String)customer1).equals("") || !((String)customer2).equals("")) {
				customer[0] = Formatting.generateCustomerNo(customer[0]);
				customer[1] = Formatting.generateCustomerNo(customer[1]);
				query.setParameter("propertyValue5", customer[0]);
				query.setParameter("propertyValue6", customer[1]);
			}
			if (!((String)saleOrder1).equals("") || !((String)saleOrder2).equals("")) {
				query.setParameter("propertyValue7", saleOrder[0]);
				query.setParameter("propertyValue8", saleOrder[1]);
			}
			if (saleDueDate1!=null || saleDueDate2!=null) {
				query.setParameter("propertyValue9", saleDueDate[0]);
				query.setParameter("propertyValue10", saleDueDate[1]);
			}
			if (!((String)customerPo1).equals("") || !((String)customerPo2).equals("")) {
				query.setParameter("propertyValue11", customerPo[0]);
				query.setParameter("propertyValue12", customerPo[1]);
			}
			if (!((String)endUser).equals("")) {
				query.setParameter("propertyValue13", endUserSelect.toUpperCase());
			}
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}
/*	public Double findQuantityByOrderAndItem(Object propertyName1,Object propertyName2) {
		EntityManagerHelper.log("finding SaleData instance with saleorder  "
				+ propertyName1 , Level.INFO, null);
		try {
			final String queryString = "select SUM(model.quantity) from SaleData model where model.id.saleOrder = " +
					":propertyValue1 and model.id.saleItem = :propertyValue2";
					
			Query query = getEntityManager().createQuery(queryString);
			query.setParameter("propertyValue1", propertyName1);
			query.setParameter("propertyValue2", propertyName2);
			//query.setParameter("propertyValue2", propertyName2);
			return (Double) query.getSingleResult();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}*/
	public SaleData findBySaleOrderAndSaleItem(Object propertyName1, Object propertyName2) {
		EntityManagerHelper.log("finding SaleData instance with saleorder and saleitem: "
				+ propertyName1 +"  "+ propertyName2, Level.INFO, null);
		try {
			final String queryString = "select model from SaleData model where model.id.saleOrder =" +
					":propertyValue1 and model.id.saleItem = :propertyValue2";
					
			Query query = getEntityManager().createQuery(queryString);
			query.setParameter("propertyValue1", propertyName1);
			query.setParameter("propertyValue2", propertyName2);
			return (SaleData) query.getSingleResult();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}
	///////////////////////////////////////////
	//////////////////////////////////////////
	/////////////////////////////////////////
	public List<SaleData> searchAmountCompleteFlag(Object propertyName1,Object propertyName2) {
		EntityManagerHelper.log("finding SaleData instance with completeFlag "
				, Level.INFO, null);
		try {
			final String queryString = "select model from SaleData model where model.completeFlag = 'X' and model.id.saleOrder = :propertyValue1 and model.id.saleItem = propertyValue2";
				
			Query query = getEntityManager().createQuery(queryString);
			query.setParameter("propertyValue1", propertyName1);
			query.setParameter("propertyValue2", propertyName2);
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}
	public Double sumCompleteAmount() {
		EntityManagerHelper.log("finding SaleData instance with completeFlag "
				, Level.INFO, null);
		try {
			final String queryString = "select SUM(model.completeAmount) from SaleData model where model.completeFlag = 'X' ";
				
			Query query = getEntityManager().createQuery(queryString);
			//query.setParameter("propertyValue1", propertyName1);
			//query.setParameter("propertyValue2", propertyName2);
			return (Double) query.getSingleResult();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}
	public List<SaleData> searchAmountNoFlag(Object propertyName1,Object propertyName2) {
		EntityManagerHelper.log("finding SaleData instance with completeFlag "
				, Level.INFO, null);
		try {
			final String queryString = "select model from SaleData model where model.completeFlag <> 'X' and model.id.saleOrder = :propertyValue1 and model.id.saleItem = propertyValue2";
				
			Query query = getEntityManager().createQuery(queryString);
			query.setParameter("propertyValue1", propertyName1);
			query.setParameter("propertyValue2", propertyName2);
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}
	public Double sumRemainAmount() {
		EntityManagerHelper.log("finding SaleData instance with completeFlag "
				, Level.INFO, null);
		try {
			final String queryString = "select SUM(model.remainAmount) from SaleData model where model.completeFlag <> 'X' ";
				
			Query query = getEntityManager().createQuery(queryString);
			//query.setParameter("propertyValue1", propertyName1);
			//query.setParameter("propertyValue2", propertyName2);
			return (Double) query.getSingleResult();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}
}
